package com.web.system.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.jdbc.ReturningWork;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.web.system.po.TreeMenuPo;
import com.web.system.po.UserPo;
import com.web.system.service.RoleService;
import com.web.system.service.TreeMenuService;

@SuppressWarnings("unchecked")
@Service("treeMenuService")
@Transactional(rollbackFor = { Exception.class })
public class TreeMenuDAO implements TreeMenuService {

	@Resource
	private SessionFactory sessionFactory;

	@Resource
	private RoleService roleService;

	/**
	 * 获取树形菜单
	 */
	public List<Map<String, Object>> syncMenu(final HttpServletRequest request,
			UserPo user, final String type) {
		Session session = null;
		List<Map<String, Object>> menuList = new ArrayList<Map<String, Object>>();
		final Map<String, Map<String, Object>> id_treenode_map = new LinkedHashMap<String, Map<String, Object>>();
		final StringBuffer hqlString = new StringBuffer();
		if ("all".equals(type)) {
			hqlString.append("select menubean1_.* from t_sys_menu menubean1_");
		} else {
			hqlString.append("select distinct menubean1_.*");
			hqlString.append("from t_sys_role_menu menulist0_ ");
			hqlString.append("inner join t_sys_menu menubean1_ ");
			hqlString.append(" on menulist0_.menu_id=menubean1_.menu_id  ");
			hqlString.append("where menulist0_.ROLE_ID ");
			hqlString.append("in( ");
			hqlString.append("select rolelist0_.ROLE_ID ");
			hqlString.append("from t_sys_user_role rolelist0_ ");
			hqlString.append("inner join t_sys_role rolebean1_ ");
			hqlString.append("on rolelist0_.ROLE_ID=rolebean1_.ROLE_ID ");
			hqlString.append("where rolelist0_.user_id=");
			hqlString.append(user.getUserId());
			hqlString.append(")");
		}
		hqlString.append(" order by menubean1_.menu_Id ");
		try {
			session = this.sessionFactory.getCurrentSession();
			menuList = session
					.doReturningWork(new ReturningWork<List<Map<String, Object>>>() {
						public List<Map<String, Object>> execute(Connection conn)
								throws SQLException {
							List<Map<String, Object>> menuList = new ArrayList<Map<String, Object>>();
							Statement stmt = null;
							stmt = conn.createStatement();
							ResultSet rs = null;
							rs = stmt.executeQuery(hqlString.toString());
							while (rs.next()) {
								String menu_id = rs.getString("menu_Id");
								String menu_name = rs.getString("menu_Name");
								String parentid = rs.getString("parentid");
								String menu_href = rs.getString("menu_Href");
								String iconCls = rs.getString("iconCls");
								if (menu_href != null && !menu_href.equals("")) {
									menu_href = menu_href.trim();
									menu_href = request.getContextPath()
											+ menu_href;
								}

								int grade = rs.getInt("grade");
								int isLeaf = rs.getInt("isLeaf");
								Map<String, Object> treeNode = new HashMap<String, Object>();
								treeNode.put("id", menu_id);
								treeNode.put("text", menu_name);
								if (grade >= 2 && isLeaf == 0) {
									treeNode.put("state", "closed");
								} else {
									treeNode.put("state", "open");
								}
								treeNode.put("isLeaf", isLeaf);
								treeNode.put("menu_name", menu_name);
								treeNode.put("menu_href", menu_href);
								treeNode.put("iconCls", iconCls);
								id_treenode_map.put(menu_id, treeNode);

								if (parentid.equals("0")) {
									menuList.add(treeNode);
								} else {
									Map<String, Object> parent_treeNode = id_treenode_map
											.get(parentid);
									if (parent_treeNode != null) {
										List<Map<String, Object>> children = null;
										if (parent_treeNode.get("children") == null) {
											children = new ArrayList<Map<String, Object>>();

										} else {
											children = (List<Map<String, Object>>) parent_treeNode
													.get("children");
										}
										children.add(treeNode);
										parent_treeNode.put("children",
												children);
									}
								}
							}
							return menuList;
						}
					});

		} catch (Exception e) {
			e.printStackTrace();
		}
		return menuList;
	}

	/**
	 * 根据menuid获取TreeMenuPo
	 */

	public TreeMenuPo getTreeMenuPoByMenuId(String MenuId) {
		// TODO Auto-generated method stub
		Session session = null;
		try {
			session = this.sessionFactory.getCurrentSession();
			TreeMenuPo treeMenuPo = (TreeMenuPo) session.get(TreeMenuPo.class,
					MenuId);
			return treeMenuPo;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * 获取菜单列表
	 */

	public List<TreeMenuPo> getMenuList(String queryString, int first,
			int pageSize) {
		// TODO Auto-generated method stub
		Session session = null;
		Query query = null;
		try {
			session = this.sessionFactory.getCurrentSession();
			query = session.createQuery(queryString);
			query.setFirstResult(first);
			query.setMaxResults(pageSize);
			List<TreeMenuPo> treeMenuPos = query.list();
			return treeMenuPos;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * 获取记录条数
	 */

	public int getTotal(String sql) {
		// TODO Auto-generated method stub
		Session session = null;
		Query query = null;
		try {
			session = this.sessionFactory.getCurrentSession();
			String queryString = "select count(*) from TreeMenuPo where 1=1 "
					+ sql;
			query = session.createQuery(queryString);
			int count = ((Long) query.uniqueResult()).intValue();
			return count;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return 0;
	}

	/**
	 * 判断该节点是否为叶子节点，叶子节点下面不能添加子菜单
	 */

	public boolean isLeafMenuId(String menuId) {
		// TODO Auto-generated method stub
		Session session = null;
		Query query = null;
		String hql = "select isLeaf from TreeMenuPo where id = '" + menuId
				+ "'";
		try {
			session = this.sessionFactory.getCurrentSession();
			query = session.createQuery(hql);
			query.setMaxResults(1);
			int count = ((Integer) query.uniqueResult()).intValue();
			return count == 1 ? true : false;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return false;
	}


	public String getAddMenuIdByParentId(String parentId) {
		// TODO Auto-generated method stub
		Session session = null;
		Query query = null;
		String hql0 = "select count(*) from TreeMenuPo where parentId = '"
				+ parentId + "'";
		String hql = "select max(cast(id as integer) + 1) from TreeMenuPo where parentId = '"
				+ parentId + "'";
		try {
			session = this.sessionFactory.getCurrentSession();
			query = session.createQuery(hql0);
			query.setMaxResults(1);
			int ret = ((Long) query.uniqueResult()).intValue();
			if (ret == 0) {
				return parentId + "01";
			}
			query = session.createQuery(hql);
			query.setMaxResults(1);
			Integer maxMenuId = (Integer) query.uniqueResult();
			return String.valueOf(maxMenuId);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * 添加数据获取菜单级别
	 */

	public int getAddGradeByParentId(String parentId) {
		// TODO Auto-generated method stub
		Session session = null;
		Query query = null;
		if ("0".equals(parentId)) {
			return 1;
		}
		String hql = "select grade from TreeMenuPo where id = '" + parentId
				+ "'";
		try {
			session = this.sessionFactory.getCurrentSession();
			query = session.createQuery(hql);
			query.setMaxResults(1);
			Integer grade = (Integer) query.uniqueResult();
			return grade + 1;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return 0;
	}


	public boolean saveOrUpdateMenu(TreeMenuPo treeMenuPo) {
		// TODO Auto-generated method stub
		Session session = null;
		try {
			session = this.sessionFactory.getCurrentSession();
			session.saveOrUpdate(treeMenuPo);
			return true;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			throw new RuntimeException();
		}
	}


	public TreeMenuPo getMenuByMenuId(String id) {
		// TODO Auto-generated method stub

		Session session = null;
		Query query = null;
		try {
			session = this.sessionFactory.getCurrentSession();
			StringBuffer hql = new StringBuffer();
			hql.append("Select a From TreeMenuPo a");
			hql.append(" left outer join fetch a.roleBeans");
			hql.append(" where a.id = '");
			hql.append(id);
			hql.append("'");
			query = session.createQuery(hql.toString());
			TreeMenuPo treeMenuPo = (TreeMenuPo) query.list().get(0);
			return treeMenuPo;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * 由于修改了菜单父结点所以，菜单的节点跟着改变，所以要先把之前的删除，在添加新的
	 */

	public boolean updateAndDeleteMenu(TreeMenuPo treeMenu,
			TreeMenuPo treeMenuPo) {
		// TODO Auto-generated method stub
		Session session = null;
		try {
			session = this.sessionFactory.getCurrentSession();
			session.delete(treeMenu);
			session.saveOrUpdate(treeMenuPo);
			return true;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			throw new RuntimeException();
		}
	}

	/**
	 * 菜单名是否被占用
	 */

	public boolean isExistMenu(String menuName) {
		// TODO Auto-generated method stub
		if (menuName == null || "".equals(menuName)
				|| "null".equalsIgnoreCase(menuName)) {
			return false;
		}
		Session session = null;
		Query query = null;
		String sql = "select count(*) from TreeMenuPo where menuName = '"
				+ menuName + "'";
		try {
			session = this.sessionFactory.getCurrentSession();
			query = session.createQuery(sql);
			query.setMaxResults(1);
			int count = ((Long) query.uniqueResult()).intValue();
			return count > 0 ? true : false;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return false;
	}

	/**
	 * 根据hql语句来执行更新操作，返回记录数
	 */

	public int executeUpdates(String sql) {
		// TODO Auto-generated method stub
		Session session = null;
		Query query = null;
		try {
			session = this.sessionFactory.getCurrentSession();
			query = session.createQuery(sql);
			int count = query.executeUpdate();
			return count;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			throw new RuntimeException();
		}
	}

}
